/**
 * 
 */
package com.agrec.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.agrec.client.actors.Name;
import com.agrec.client.actors.Position;
import com.agrec.client.actors.User;

/**
 * @author Alaa Gharbawi
 * This class Deals with the mySql Database. Every request to DB has to go through this Class.
 * This class is a singleton class; Only one instance running in the server
 *
 */
public class DatabaseManager {
	private static DatabaseManager _instance;
	protected static final String db = "agrec";
	protected static final String username = "agrec";
	protected static final String password = "agrec";
	protected static final String urlString = "jdbc:mysql://localhost/"+db+"?user="+username+"&password="+password;
	

	   /** Get a singleton instance.  Not incredibly useful here as everything
	    *  is static data.
	    */
	public static synchronized DatabaseManager instance() {

		if(_instance == null) {
	    	_instance = new DatabaseManager();

	    	try {
	            // load driver
	            Class.forName("com.mysql.jdbc.Driver").newInstance();
	    	}catch (Exception e) {
	            System.err.println("Can't find driver");
	            //System.exit(1);
	    	}
		}
		return _instance;
	}

	public static User getUser(String userName) throws SQLException {
		
		User user = null;
		Connection con = null;
		Statement stmt = null;
		try {
			System.out.println(urlString);
			con = DriverManager.getConnection(urlString);
			stmt = con.createStatement();
			ResultSet result = stmt.executeQuery ("SELECT * " +
													"FROM User" +
													" WHERE userName='" + userName + "';");

			if(result.next()){
				user = new User();
				user.id(result.getInt("id"));
				user.userName(result.getString("userName"));
				user.password(result.getString("password"));
				user.email(result.getString("email"));
				user.phone(result.getString("phone"));
				user.ext(result.getString("ext"));
				user.role(result.getString("role"));
				//user.token(result.getString("token"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			//throw new SQLException(e.toString());
		} finally {
			stmt.close();
			con.close();
		}
		
		

		return user;
	}

	public static void addUserToken(int id, String token) {
		// TODO Auto-generated method stub
		
	}

	public static Boolean addCandidate(Name candidate) {
		
		
		return null;
	}

	public static ArrayList<Position> getRecentOpportunities(int numOfPos) throws SQLException {
		ArrayList<Position> positions = null;
		Connection con = null;
		Statement stmt = null;
		try {
			con = DriverManager.getConnection(urlString);
			stmt = con.createStatement();
			ResultSet result = stmt.executeQuery ("SELECT * " +
													"FROM Position" + ";");
													//" WHERE userName='" + userName + "';");

			positions = new ArrayList<Position>();
			while(result.next()){
				Position pos = new Position();
				pos.id(result.getInt("id"));
				pos.companyID(result.getInt("companyID"));
				pos.jobTitle(result.getString("jobTytle"));
				pos.location(result.getString("location"));
				pos.minSalary(result.getInt("minSalary"));
				pos.maxSalary(result.getInt("maxSalary"));
				pos.datePosted(result.getDate("datePosted"));
				pos.city(result.getString("city"));
				pos.contactName(result.getString("contactName"));
				pos.contactEmail(result.getString("contactEmail"));
				pos.userName(result.getString("userName"));
				pos.otherBenefits(result.getString("otherBenefits"));
				pos.notes(result.getString("notes"));
				//pos.showOnWeb(result.getString("showOnWeb"));
				pos.nationalities(result.getString("nationalities"));
				pos.visasAvailable(result.getString("visasAvailable"));
				pos.minAge(result.getInt("minAge"));
				pos.maxAge(result.getInt("maxAge"));
				//pos.gender(result.getString("gender"));
				pos.fringBenifits(result.getString("fringBenifits"));
				pos.variablePayStructure(result.getDouble("variablePayStructure"));
				pos.jobResponsabilities(result.getString("jobResponsabilities"));
				pos.reportsInto(result.getString("reportsInto"));
				pos.directReport(result.getString("directReport"));
				pos.backgroundHistory(result.getString("backgroundHistory"));
				pos.keySellingProposition(result.getString("keySellingProposition"));
				pos.experience(result.getString("experience"));
				pos.personality(result.getString("personality"));
				pos.jobType(result.getString("jobType"));
				pos.attach(result.getString("attach"));
				pos.industry_speciality(result.getString("industry_speciality"));
				
				positions.add(pos);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			//throw new SQLException(e.toString());
		} catch (Exception e1) {
			e1.printStackTrace();
		} finally {
			stmt.close();
			con.close();
		}

		return positions;
	}

}
